﻿/*
	This SQL provides information to populate both Routines (Functions & Stored Procedures) along with their columns.
*/
SELECT     INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_CATALOG, INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_SCHEMA, 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_NAME, INFORMATION_SCHEMA.ROUTINE_COLUMNS.COLUMN_NAME, 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.ORDINAL_POSITION, INFORMATION_SCHEMA.ROUTINE_COLUMNS.COLUMN_DEFAULT, 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.IS_NULLABLE, INFORMATION_SCHEMA.ROUTINE_COLUMNS.DATA_TYPE, 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.CHARACTER_MAXIMUM_LENGTH, INFORMATION_SCHEMA.ROUTINE_COLUMNS.CHARACTER_OCTET_LENGTH, 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.NUMERIC_PRECISION, INFORMATION_SCHEMA.ROUTINE_COLUMNS.NUMERIC_PRECISION_RADIX, 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.NUMERIC_SCALE, INFORMATION_SCHEMA.ROUTINE_COLUMNS.DATETIME_PRECISION, 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.CHARACTER_SET_CATALOG, INFORMATION_SCHEMA.ROUTINE_COLUMNS.CHARACTER_SET_SCHEMA, 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.CHARACTER_SET_NAME, INFORMATION_SCHEMA.ROUTINE_COLUMNS.COLLATION_CATALOG, 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.COLLATION_SCHEMA, INFORMATION_SCHEMA.ROUTINE_COLUMNS.COLLATION_NAME, 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.DOMAIN_CATALOG, INFORMATION_SCHEMA.ROUTINE_COLUMNS.DOMAIN_SCHEMA, 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.DOMAIN_NAME, 
                      COLUMNPROPERTY(OBJECT_ID(INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_SCHEMA + '.' + INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_NAME), 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.COLUMN_NAME, 'IsIdentity') AS IS_IDENTITY, 
                      COLUMNPROPERTY(OBJECT_ID(INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_SCHEMA + '.' + INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_NAME), 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.COLUMN_NAME, 'IsComputed') AS IS_COMPUTED
FROM         INFORMATION_SCHEMA.ROUTINE_COLUMNS WITH (NOLOCK) INNER JOIN
                      INFORMATION_SCHEMA.ROUTINES WITH (NOLOCK) ON 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.ROUTINES.ROUTINE_SCHEMA AND 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_CATALOG = INFORMATION_SCHEMA.ROUTINES.ROUTINE_CATALOG AND 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.ROUTINES.ROUTINE_NAME
WHERE     (INFORMATION_SCHEMA.ROUTINES.DATA_TYPE = 'TABLE') OR
                      (INFORMATION_SCHEMA.ROUTINES.DATA_TYPE IS NULL)
ORDER BY INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_SCHEMA, INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_NAME, 
                      INFORMATION_SCHEMA.ROUTINE_COLUMNS.ORDINAL_POSITION


